package DBLayer;
import Model.Supplier;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;


public class DBSupplier implements IFDBSupplier{
    private  Connection connection;
    /** Creates a new instance of DBSupplier */
    public DBSupplier() {
      connection = DbConnection.getInstance().getDBconnection();
    }
    
  //Implements the methods from the interface
    
    // get all suppliers
    public ArrayList<Supplier> getAllSupplier(int retriveAssociation)
    {
        return ArrayList<Supplier>("", retriveAssociation);
    }
    //get one employee having the ssn
    public Supplier findSupplier(int supplierID, int retriveAssociation)
    {   String wClause = " supplierID = '" + supplierID + "'";
        return supplierID(wClause, retriveAssociation);
    }
    //find one employee having the fname
    public Supplier searchSupplierFname(String attValue, boolean retriveAssociation)
    {
        String wClause = "fname like '%" + attValue + "%'";
        System.out.println("SearchEmployye " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
      //find one employee having the lname
    public Supplier searchSupplierLname(String attValue, boolean retriveAssociation)
    {
        String wClause = "lname = '" + attValue + "'";
        System.out.println("SearchEmployye " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
   
     //insert a new supplier
    @Override
    public int insertSupplier(Supplier supplier) throws Exception
    {  //call to get the next ssn number
        int nextSSN = GetMax.getMaxId("Select max(ssn) from employee");
        nextSSN = nextSSN + 1;
        System.out.println("next ssn = " +  nextSSN);
  
       int rc = -1;
	   String query="INSERT INTO employee(fname, lname, ssn,superssn,dno)  VALUES('"+
	  		supplier.getFname()  + "','"  +
		    supplier.getLname()  + "','"  +
            nextSSN + "','" +
            supplier.getSupervisor().getSsn() + "',5)";
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Supplier is not found");
          throw new Exception ("Supplier is not inserted correct");
       }
       return(rc);
    }
@Override
	public int updateEmployee(Supplier supplier)
	{
		Supplier supplierObj  = supplier;
		int rc=-1;

		String query="UPDATE employee SET "+
		 	  "fname ='"+ supplierObj.getFname()+"', "+
		 	  "lname ='"+ supplierObj.getLname() + "', " +
                          "salary ='"+ supplierObj.getSalary() + "', " +
                          "superssn ='"+ supplierObj.getSupervisor().getSsn() + "' " +
		          " WHERE ssn = '"+ supplierObj.getSsn() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}// try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in supplier database: "+ex);
	  	}
		return(rc);
	}
	
	public int delete(String ssn)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM supplier WHERE ssn = '" +
				ssn + "'";
                System.out.println(query);
	  	try{ // delete from supplier
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}// try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in supplier database: "+ex);
   	        }
		return(rc)raw count;
	}
	
	//private methods
    //michWere is used whenever we want to select more than one employee
	
	 
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the supplier from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Supplier supplierObj = new Supplier();
		 supplierObj = buildSupplier(results);	
                 list.add(supplierObj);	
		}//end while
                 stmt.close();     
                 if(retrieveAssociation)
                 {   //The supervisor and department is to be build as well
                     for(Supplier supplierObj : list){
                        String superssn = supplierObj.getSupervisor().getSsn();
                        Supplier superEmp = singleWhere(" ssn = '" + superssn + "'",false);
                        supplierObj.setSupervisor(superEmp);
                        System.out.println("Supervisor is selected");
                       // here the department has to be selected as well
                     }
                 }//end if   
			
		}// try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singlewhere is used when we only select one employee 	
	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supplierObj = new Supplier();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the supplier from the database
	 		Statement stmt = connection.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            supplierObj = buildSupplier(results);
                            //association is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The supervisor and department is to be build as well
                                String superssn = supplierObj.getSupervisor().getSsn();
                                Supplier superSupplier = singleWhere(" ssn = '" + superssn + "'",false);
                                supplierObj.setSupervisor(superSupplier);
                                System.out.println("Supervisor is selected");
                               // here the department has to be selected as well
                           
                           
                            }
			}
                        else{ //no supplier was found
                            supplierObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supplierObj;
	}
	
	//method to build the query
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT fname, minit,lname,ssn, address, bdate,sex, salary, superssn,dno  FROM employee";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	//method to build an employee object
	
	private Supplier buildSupplier(ResultSet results)
      {   Supplier supplierObj = new Supplier();
          try{ // the columns from the table emplayee  are used
                supplierObj.setSupplier(results.getInt("SupplierID"));
                supplierObj.setName(results.getString("Name"));
                supplierObj.setAddress(results.getString("Address"));
                supplierObj.setCountry(results.getString("Country"));
                supplierObj.setPhoneno(results.getInt("Phoneno"));
                supplierObj.setEmail(results.getString("Email"));
                
          }
         catch(Exception e)
         {
             System.out.println("error in building the supplier object");
         }
         return supplierObj;
      }

	
}  